package com.mp.approvalreq.dao.impl;

import com.mp.approvalreq.dao.OutdoorDao;
import com.mp.approvalreq.entity.Outdoor;
import com.mp.approvalreq.util.ObjectUtils;
import com.plusmoney.util.SQLLogger;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.*;

public class OutdoorDaoImpl extends AbstractDao implements OutdoorDao {

    private static final Logger logger = LoggerFactory.getLogger(OutdoorDaoImpl.class);

    private Connection connection;

    public OutdoorDaoImpl(Connection connection) {
        this.connection = connection;
    }

    private PreparedStatement getPreparedStatement(String sql) throws SQLException {
        return connection.prepareStatement(sql);
    }

    @Override
    String getQuerySql(String condition) {
        final String query = "select id, company_id, company_info_id, request_id, start_time,"
                + " end_time, src_address, src_coordinate, dest_address, dest_coordinate,"
                + " sign_offset, reason, transportation, stay_day, attach_file,"
                + " type"
                + " from sys_approval_outdoor";
        return String.format("%s %s", query, condition);
    }

    @Override
    Outdoor getObject(ResultSet rs) throws SQLException {
        Outdoor outdoor = new Outdoor();
        outdoor.setId(rs.getInt(1));
        outdoor.setCompanyId(rs.getInt(2));
        outdoor.setCompanyInfoId(rs.getInt(3));
        outdoor.setRequestId(rs.getInt(4));
        outdoor.setStart(rs.getTimestamp(5));
        outdoor.setStartTime(getTimeStr(rs.getTimestamp(5)));

        outdoor.setEnd(rs.getTimestamp(6));
        outdoor.setEndTime(getTimeStr(rs.getTimestamp(6)));
        outdoor.setSrcAddress(rs.getString(7));
        outdoor.setSrcCoordinate(rs.getString(8));
        outdoor.setDestAddress(rs.getString(9));
        outdoor.setDestCoordinate(rs.getString(10));

        outdoor.setSignOffset(rs.getInt(11));
        outdoor.setReason(rs.getString(12));
        outdoor.setTransportation(rs.getString(13));
        outdoor.setStayDay(rs.getString(14));
        outdoor.setAttachFile(rs.getString(15));

        outdoor.setType(rs.getInt(16));
        return outdoor;
    }

    private String getListQuerySql(String condition) {
        final String query = "select company_id, company_info_id, request_id, start_time, end_time,"
                + " type, reason, src_address, src_coordinate, dest_address,"
                + " dest_coordinate, sign_offset"
                + " from sys_approval_outdoor";
        return String.format("%s %s", query, condition);
    }

    private Outdoor getListObject(ResultSet rs) throws SQLException {
        Outdoor outdoor = new Outdoor();
        outdoor.setCompanyId(rs.getInt(1));
        outdoor.setCompanyInfoId(rs.getInt(2));
        outdoor.setRequestId(rs.getInt(3));
        outdoor.setStart(rs.getTimestamp(4));
        outdoor.setStartTime(getTimeStr(rs.getTimestamp(4)));
        outdoor.setEnd(rs.getTimestamp(5));
        outdoor.setEndTime(getTimeStr(rs.getTimestamp(5)));

        outdoor.setType(rs.getInt(6));
        outdoor.setReason(rs.getString(7));
        outdoor.setSrcAddress(rs.getString(8));
        outdoor.setSrcCoordinate(rs.getString(9));
        outdoor.setDestAddress(rs.getString(10));

        outdoor.setDestCoordinate(rs.getString(11));
        outdoor.setSignOffset(rs.getInt(12));
        return outdoor;
    }

    @Override
    public void insertOutdoor(Outdoor outdoor) throws SQLException {
        ObjectUtils.initInteger(outdoor);
        String sql = "insert into sys_approval_outdoor set"
                + " company_id = ?, company_info_id = ?, request_id = ?, start_time = ?, end_time = ?,"
                + " src_address = ?, src_coordinate = ?, dest_address = ?, dest_coordinate = ?, sign_offset = ?,"
                + " reason = ?, transportation = ?, stay_day = ?, attach_file = ?, type = ?,"
                + " duration = ?, approval_num = ?, create_time = now()";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, outdoor.getCompanyId());
            ps.setInt(2, outdoor.getCompanyInfoId());
            ps.setInt(3, outdoor.getRequestId());
            ps.setString(4, outdoor.getStartTime());
            ps.setString(5, outdoor.getEndTime());

            ps.setString(6, outdoor.getSrcAddress());
            ps.setString(7, outdoor.getSrcCoordinate());
            ps.setString(8, outdoor.getDestAddress());
            ps.setString(9, outdoor.getDestCoordinate());
            ps.setInt(10, outdoor.getSignOffset());

            ps.setString(11, outdoor.getReason());
            ps.setString(12, outdoor.getTransportation());
            ps.setString(13, outdoor.getStayDay());
            ps.setString(14, outdoor.getAttachFile());
            ps.setInt(15, outdoor.getType());

            ps.setInt(16, outdoor.getDuration());
            ps.setString(17, outdoor.getApprovalNum());
            logger.info(SQLLogger.logSQL(sql, outdoor.getCompanyId(), outdoor.getCompanyInfoId(), outdoor.getRequestId(),
                    outdoor.getStartTime(), outdoor.getEndTime(), outdoor.getSrcAddress(), outdoor.getSrcCoordinate(),
                    outdoor.getDestAddress(), outdoor.getDestCoordinate(), outdoor.getSignOffset(), outdoor.getReason(),
                    outdoor.getTransportation(), outdoor.getStayDay(), outdoor.getAttachFile(), outdoor.getType(),
                    outdoor.getDuration(), outdoor.getApprovalNum()));

            ps.executeUpdate();
        }
    }

    @Override
    public void updateDestInfo(String destAddress, String destCoordinate, Integer requestId) throws SQLException {
        String sql = "update sys_approval_outdoor set dest_address = ?, dest_coordinate = ? where request_id = ? limit 1";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setString(1, destAddress);
            ps.setString(2, destCoordinate);
            ps.setInt(3, requestId);
            logger.info(SQLLogger.logSQL(sql, destAddress, destCoordinate, requestId));

            ps.executeUpdate();
        }

    }

    @Override
    public void deleteOutdoorByRequestId(Integer requestId, Integer companyId) throws SQLException {
        String sql = "update sys_approval_outdoor set state = 2, update_time = now() where request_id = ? and company_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, requestId);
            ps.setInt(2, companyId);
            logger.info(SQLLogger.logSQL(sql, requestId, companyId));

            ps.executeUpdate();
        }
    }

    @Override
    public void deleteOutdoor(Outdoor outdoor) throws SQLException {
        String sql = "update sys_approval_outdoor set state = 2, update_time = now() where id = ? and company_id = ? limit 1";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, outdoor.getId());
            ps.setInt(2, outdoor.getCompanyId());
            logger.info(SQLLogger.logSQL(sql, outdoor.getId(), outdoor.getCompanyId()));

            ps.executeUpdate();
        }
    }

    @Override
    public Outdoor selectLastOutdoorByRequestId(Integer requestId) throws SQLException {
        String sql = getQuerySql("where request_id = ? order by id desc limit 1");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, requestId);
            logger.info(SQLLogger.logSQL(sql, requestId));

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return getObject(rs);
                }
            }
        }
        return null;
    }

    @Override
    public List<Integer> selectExistOutdoorCompanyIdsByDateScope(String start, String end) throws SQLException {
        List<Integer> list = new ArrayList<>();
        String sql = "select distinct(company_id) from sys_approval_outdoor where start_time >= ? and end_time <= ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setString(1, start);
            ps.setString(2, end);
            logger.info(SQLLogger.logSQL(sql, start, end));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public List<Outdoor> selectCompanyOutdoorsByDateScope(Integer companyId, String startTime, String endTime) throws SQLException {
        List<Outdoor> list = new ArrayList<>();
        String sql = getListQuerySql("where company_id = ? and not(end_time <= ? or start_time >= ?) and state = 1");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setString(2, startTime);
            ps.setString(3, endTime);
            logger.info(SQLLogger.logSQL(sql, companyId, startTime, endTime));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getListObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public List<Outdoor> selectBatchUserOutdoorsByDateScope(Integer companyId, String companyInfoIds, String startTime, String endTime) throws SQLException {
        List<Outdoor> list = new ArrayList<>();
        String sql = getListQuerySql("where company_id = ? and company_info_id in(" + companyInfoIds + ")"
                + " and not(end_time <= ? or start_time >= ?) and state = 1");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setString(2, startTime);
            ps.setString(3, endTime);
            logger.info(SQLLogger.logSQL(sql, companyId, startTime, endTime));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getListObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public Map<Integer, Outdoor> selectOutdoorsByRequestIds(String requestIds) throws SQLException {
        Map<Integer, Outdoor> map = new HashMap<>();
        if (StringUtils.isEmpty(requestIds)) {
            return map;
        }
        String sql = getQuerySql("where request_id in(" + requestIds + ") order by id");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            logger.info(sql);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Outdoor outdoor = getObject(rs);
                    map.put(outdoor.getRequestId(), outdoor);
                }
            }
        }
        return map;
    }
}
